package com.example.tool.util;

import com.alibaba.fastjson.JSONObject;
import com.example.tool.service.RedisService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.Collections;
import java.util.List;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

@Component
public class WriteExcelUtil {
    @Autowired
    private RedisService redisService;
    private static final Integer readBatchSize = 3000;
    private static final Integer updateBatchSize = 3000;
    //保存时间值
    private static final Integer dataSaveDays = 7;
    //保存时间的单位
    private static final TimeUnit timeUnit = TimeUnit.DAYS;

    /**
     * 导入数据
     *
     * @param workbookId
     * @param secret
     * @param token
     * @param keyTemplate
     * @param values
     * @param sheetId
     * @param content
     * @param platform
     * @param headerValues
     */
    public void appendData(String workbookId, String secret, String token, String keyTemplate, List<List<String>> values, String sheetId, String content, Integer platform, List<String> headerValues, String rangeAddressTemplate) {
        String rangeAddressKey = String.format(keyTemplate, workbookId, sheetId);
//        String content=values.stream().map(v->"原始单号："+v.get(0)+" ;出库单号："+v.get(1)+" ;物流公司："+v.get(2)+" ;物流单号："+v.get(3)).collect(Collectors.joining("\n"));
//        DDUtil.sendMessageWebhook(secret,token,content);
        if (platform == 1) {
            Integer currentSubscript = ddOptimizeTable(rangeAddressKey, workbookId, sheetId, headerValues, rangeAddressTemplate);
            ddImportValuesData(workbookId, sheetId, values, currentSubscript, rangeAddressKey, rangeAddressTemplate);
            DDUtil.sendMessageWebhook(secret, token, content);
        } else {
            Integer currentSubscript = qwOptimizeTable(rangeAddressKey, workbookId, sheetId, headerValues, rangeAddressTemplate);
            qwImportValuesData(workbookId, sheetId, values, currentSubscript, rangeAddressKey, rangeAddressTemplate);
            WeChatUtil.sendTextMessage(secret, content, false);
        }
    }

    /**
     * 获取上次企业微信excle更新的下标
     *
     * @param rangeAddressKey
     * @param rangeAddressKey
     * @param workbookId
     * @param sheetId
     * @return
     */
    public Integer ddOptimizeTable(String rangeAddressKey, String workbookId, String sheetId, List<String> headerValues, String rangeAddressTemplate) {
        String currentSubscriptStr = redisService.getString(rangeAddressKey);
        Integer currentSubscript = 1;
        if (StringUtils.isBlank(currentSubscriptStr)) {
            List<String> findSheets = DDUtil.getSheets(workbookId).getJSONArray("value").stream().map(val -> {
                JSONObject vm = (JSONObject) val;
                return vm.getString("name");
            }).filter(name -> name.equals(sheetId)).collect(Collectors.toList());
            if (!findSheets.isEmpty()) {
                Integer startIndex = currentSubscript;
                Integer endIndex = startIndex + readBatchSize;
                currentSubscript = 0;
                for (int i = 0; i < 100; i++) {
                    String rangeAddress = String.format(rangeAddressTemplate, startIndex, endIndex);
                    JSONObject rsData = JSONObject.parseObject(DDUtil.readSheets(workbookId, sheetId, rangeAddress));
                    long rssData = rsData.getJSONArray("values").stream().map(v -> (List<Object>) v).filter(v -> StringUtils.isNotBlank(v.get(0).toString())).count();
                    currentSubscript = Math.toIntExact(currentSubscript + rssData);
                    if (rssData == 0) {
                        break;
                    }
                    startIndex = endIndex + 1;
                    endIndex = endIndex + readBatchSize;
                }
                redisService.setWithExpire(rangeAddressKey, currentSubscript.toString(), dataSaveDays, timeUnit);
            } else {
                DDUtil.createSheets(workbookId, sheetId);
                String rangeAddress = String.format(rangeAddressTemplate, currentSubscript, currentSubscript);
                DDUtil.updateRange(workbookId, sheetId, rangeAddress, Collections.singletonList(headerValues));
                redisService.setWithExpire(rangeAddressKey, currentSubscript.toString(), dataSaveDays, timeUnit);
            }
        } else {
            currentSubscript = Integer.parseInt(currentSubscriptStr);
        }
        return currentSubscript;
    }

    /**
     * 数据更新到钉钉excel
     *
     * @param workbookId
     * @param sheetId
     * @param values
     * @param currentSubscript
     * @param rangeAddressKey
     */
    public void ddImportValuesData(String workbookId, String sheetId, List<List<String>> values, Integer currentSubscript, String rangeAddressKey, String rangeAddressTemplate) {
        int totalSize = values.size();
        Integer startIndex = currentSubscript;
        Integer endIndex = startIndex;
        for (int start = 0; start < totalSize; start += updateBatchSize) {
            int end = Math.min(start + updateBatchSize, totalSize);
            List<List<String>> batchValues = values.subList(start, end);
            startIndex = endIndex + 1;
            endIndex = startIndex + batchValues.size() - 1;
            String rangeAddress = String.format(rangeAddressTemplate, startIndex, endIndex);
            DDUtil.updateRange(workbookId, sheetId, rangeAddress, batchValues);
        }
        redisService.setWithExpire(rangeAddressKey, endIndex.toString(), dataSaveDays, timeUnit);
    }


    /**
     * 获取上次企业微信excle更新的下标
     *
     * @param rangeAddressKey
     * @param workbookId
     * @param sheetId
     * @param headerValues
     * @return
     */
    public Integer qwOptimizeTable(String rangeAddressKey, String workbookId, String sheetId, List<String> headerValues, String rangeAddressTemplate) {
        return 0;
    }

    /**
     * 更新数据到企业微信excle
     *
     * @param workbookId
     * @param sheetId
     * @param values
     * @param currentSubscript
     * @param rangeAddressKey
     */
    public void qwImportValuesData(String workbookId, String sheetId, List<List<String>> values, Integer currentSubscript, String rangeAddressKey, String rangeAddressTemplate) {
        int totalSize = values.size();
        Integer startIndex = currentSubscript;
        Integer endIndex = startIndex;
        for (int start = 0; start < totalSize; start += updateBatchSize) {
            int end = Math.min(start + updateBatchSize, totalSize);
            List<List<String>> batchValues = values.subList(start, end);
            startIndex = endIndex + 1;
            endIndex = startIndex + batchValues.size() - 1;
            String rangeAddress = String.format(rangeAddressTemplate, startIndex, endIndex);
            DDUtil.updateRange(workbookId, sheetId, rangeAddress, batchValues);
        }
        redisService.setWithExpire(rangeAddressKey, endIndex.toString(), dataSaveDays, timeUnit);
    }

}
